package cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.service;

import cn.edu.buaa.cnsatm.traffic_big_data_server.util.*;
import cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.dao.OceanDAO;
import cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.dao.PortDAO;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import org.apache.activemq.artemis.api.core.Pair;
import org.geotools.resources.JDBC;

import javax.xml.transform.Result;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PortService {
    public static String getTopKPortCongestion(int k) throws SQLException {
        /**
         * @author LX
         * @description 查询港口拥挤度
         * @date 2019-11-06
         */
        ResultSet resultSet = PortDAO.getTopKPortCongestion(k);
        JsonArray ja = new JsonArray();
        while (resultSet.next()){
            JsonObject jo = new JsonObject();
            jo.addProperty("port_name", resultSet.getString("port_name"));
            jo.addProperty("congestion",resultSet.getString("congestion"));
            ja.add(jo);
        }
        resultSet.getStatement().close();
        resultSet.close();
        return ja.toString();
    }
    //http://www.shipxy.com/Ship/GetAtPortShips?points=114170095.64671057,22649967,114356938.35328941,22649967,114356938.35328941,22483967.000000004,114170095.64671057,22483967.000000004
    //http://www.shipxy.com/Ship/GetAtPortShips?points= 乘1e6
    // 114170095.64671057,22649967,小大
    // 114356938.35328941,22649967,大大
    // 114356938.35328941,22483967.000000004,大小
    // 114170095.64671057,22483967.000000004 小小
    // det longitude= -0.093 det latitude = -0.083‬
    public static String getPortCongestionFromWebAndSaveToDatabase(String cookie) throws SQLException {
        /**
         * @author LX
         * @description 爬取港口拥挤度，需要注册船讯网，然后登陆后把cookie拷出来作为参数
         * @date 2019-11-？
         */
        String portCongestionSQLString = new String("-- 港口拥挤度\n");
        final String PORT_CONGESTION_URL_LEFT = "http://www.shipxy.com/Ship/GetAtPortShips?points=";
        final int[][] det = {{1,-1},{1,-1}};
        ResultSet resultSet = PortDAO.getAllPortNameAndLocation();
        while (resultSet.next()){
            String portName = resultSet.getString("port_name");
            Point2D locPoint = GeometryToPoint2DUtil.geometryToPoint(resultSet.getString("geom"));
            Point2D p1 = new Point2D(locPoint.getX() - 0.093, locPoint.getY() + 0.083);
            Point2D p2 = new Point2D(locPoint.getX() + 0.093, locPoint.getY() + 0.083);
            Point2D p3 = new Point2D(locPoint.getX() + 0.093, locPoint.getY() - 0.083);
            Point2D p4 = new Point2D(locPoint.getX() - 0.093, locPoint.getY() - 0.083);
            p1 = p1.multiply(1000000);
            p2 = p2.multiply(1000000);
            p3 = p3.multiply(1000000);
            p4 = p4.multiply(1000000);
            String pointString = String.valueOf(p1.getX()) + "," + String.valueOf(p1.getY()) + "," +
                    String.valueOf(p2.getX()) + "," + String.valueOf(p2.getY()) + "," +
                    String.valueOf(p3.getX()) + "," + String.valueOf(p3.getY()) + "," +
                    String.valueOf(p4.getX()) + "," + String.valueOf(p4.getY());
            //System.out.println(PORT_CONGESTION_URL_LEFT + pointString);
            JSONObject jo = JSON.parseObject(WebSpiderUtil.getJsonFromURL(PORT_CONGESTION_URL_LEFT + pointString, cookie));
            //jo.getJSONArray("data");
            String sql = "UPDATE port_point set congestion = " + String.valueOf(jo.getJSONArray("data").size() / 1500.0) +
                    " WHERE port_name = '" + portName + "';\n";
            portCongestionSQLString = portCongestionSQLString + sql;
            //System.out.println(jo.getJSONArray("data").size());
        }
        resultSet.getStatement().close();
        resultSet.close();
        return portCongestionSQLString;
    }
    public static void getBoatNumInCellFromWebAndSaveToDatabase(String cookie)throws SQLException{
        /**
         * @author LX
         * @description 爬取各个海区格子中船的数量/格子面积，然后登陆后把cookie拷出来作为参数
         * @date 2019-11-？
         */
        String portCongestionSQLString = new String("-- 港口拥挤度\n");
        final String PORT_CONGESTION_URL_LEFT = "http://www.shipxy.com/Ship/GetAtPortShips?points=";
        final int[][] det = {{1,-1},{1,-1}};
        ResultSet resultSet = OceanDAO.getAllOceanCell();
        String updateTime = DateUtil.getCurrentTime();
        while (resultSet.next()) {
            String cellID = resultSet.getString("cell_id");
            String cellDetail = resultSet.getString("detail");
            JSONObject detailJo = JSON.parseObject(cellDetail);
            JSONArray ja = detailJo.getJSONObject("geometry").getJSONArray("coordinates").getJSONArray(0).getJSONArray(0);
            List<Point2D> pointList = new ArrayList<>();
            for(int i = 0; i < ja.size(); i++){
                JSONArray coordinate = ja.getJSONArray(i);
                pointList.add(new Point2D(coordinate.getDouble(0) * 1000000, coordinate.getDouble(1) * 1000000));
            }
            String pointString = pointList.get(0).getX() + "," + pointList.get(0).getY();
            for(int i = 1; i < pointList.size(); i++){
                pointString = pointString + "," + pointList.get(i).getX() + "," + pointList.get(i).getY();
            }
            double cellArea = resultSet.getDouble("area");
            JSONObject jo = JSON.parseObject(WebSpiderUtil.getJsonFromURL(PORT_CONGESTION_URL_LEFT + pointString, cookie));
            double boatDensity = jo.getJSONArray("data").size() / cellArea;
            OceanDAO.insertBoatInCell(cellID, boatDensity, updateTime);

            //System.out.println();
            //double cellArea = ComputeGeometryUtil.getAreaByLonAndLatList(pointList) * 1000000;
//            OceanDAO.updateCellAreaByCellID(cellID, cellArea);
//            String portName = resultSet.getString("port_name");
//            Point2D locPoint = GeometryToPoint2DUtil.geometryToPoint(resultSet.getString("geom"));
//            Point2D p1 = new Point2D(locPoint.getX() - 0.093, locPoint.getY() + 0.083);
//            Point2D p2 = new Point2D(locPoint.getX() + 0.093, locPoint.getY() + 0.083);
//            Point2D p3 = new Point2D(locPoint.getX() + 0.093, locPoint.getY() - 0.083);
//            Point2D p4 = new Point2D(locPoint.getX() - 0.093, locPoint.getY() - 0.083);
//            p1 = p1.multiply(1000000);
//            p2 = p2.multiply(1000000);
//            p3 = p3.multiply(1000000);
//            p4 = p4.multiply(1000000);
//            String pointString = String.valueOf(p1.getX()) + "," + String.valueOf(p1.getY()) + "," +
//                    String.valueOf(p2.getX()) + "," + String.valueOf(p2.getY()) + "," +
//                    String.valueOf(p3.getX()) + "," + String.valueOf(p3.getY()) + "," +
//                    String.valueOf(p4.getX()) + "," + String.valueOf(p4.getY());
//            //System.out.println(PORT_CONGESTION_URL_LEFT + pointString);
//            JSONObject jo = JSON.parseObject(WebSpiderUtil.getJsonFromURL(PORT_CONGESTION_URL_LEFT + pointString, cookie));
//            //jo.getJSONArray("data");
//            String sql = "UPDATE port_point set congestion = " + String.valueOf(jo.getJSONArray("data").size() / 1500.0) +
//                    " WHERE port_name = '" + portName + "';\n";
//            portCongestionSQLString = portCongestionSQLString + sql;
//            //System.out.println(jo.getJSONArray("data").size());
        }
        resultSet.getStatement().close();
        resultSet.close();
       //return portCongestionSQLString;
    }
    public  static void main(String args[]) throws SQLException {
        JDBCUtil.startConnection();
        getBoatNumInCellFromWebAndSaveToDatabase("Hm_lvt_adc1d4b64be85a31d37dd5e88526cc47=1575720236; gdxidpyhxdE=4qA6OC4sUaeGJgS9mh%2Bw1V8oALPUdECN%2BOY0klbCd8KbLg1QAppG%5CTVUN1nH08%2BLTQr2oCW5oVzqM1GK%2F4j28EcJ9X1b0y9X5Ye%2BxYzoUgUoLhaNH64GOa%5CI2KpibXO90z0cizYnhEu3oHgEvb%5C5PQTeqHtSz76%5C85fWdBtr086w5OPx%3A1571904561957; _9755xjdesxxd_=32; YD00803672648830%3AWM_NI=81t9lDDIUBRZLydCsf%2BJdV3QkUTIkGp1MRgo4EMtQJUGsB9XHuKgIhUP%2FqvAyZRNuOZBo6Tp0SQd%2FuQOpkPg3QQjp9qgKSCKf9BGq%2BSMiUi47Xvy8bEcriFAz0HgC%2BKsTFc%3D; YD00803672648830%3AWM_NIKE=9ca17ae2e6ffcda170e2e6eeafcc3d94b69a97b579fb968eb6d45f928e9bbbbb34b6afffd9c83caa9082abf82af0fea7c3b92afcefbcdaef5088979ba3d15290a7aca4f26ee996acb7b25ba98b9caaea4db4ef83b3e665f89b8395d4429799bda3bc548cab8588cc649790e192f54bbb869eaff8459a9ea1d7d77cf48d9f85ae7f9391c08cd36df18f9f8bf333b3b985b4c241a6e78ea8f4219193a7b6ec40f588ba9abb6a87bdbe8ac54bbaf08888c22189ad9ca7e237e2a3; YD00803672648830%3AWM_TID=fg1N%2BePInd1EUQAAURZ446qk9ExzOcJv; FD857C2AF68165D4=/vvpckUpWYZsqYsPKEXXd7f9BJX3hfosOWT9KsX+q3QAkIQ/97TJzECFJNcBuTM7uSkypamgotk=; SERVERID=8dac9e937b8701fba4cb1394cffade3e|1575720257|1575720227; _elane_shipfilter_country=0%2C1%2C2; _elane_shipfilter_one=2; _elane_maptype=MT_SEA; Hm_lpvt_adc1d4b64be85a31d37dd5e88526cc47=1575720236; tc_TC=; _elane_shipfilter_type=%u8D27%u8239%2C%u96C6%u88C5%u7BB1%u8239%2C%u6CB9%u8F6E%2C%u5F15%u822A%u8239%2C%u62D6%u8F6E%2C%u62D6%u5F15%2C%u6E14%u8239%2C%u6355%u635E%2C%u5BA2%u8239%2C%u641C%u6551%u8239%2C%u6E2F%u53E3%u4F9B%u5E94%u8239%2C%u88C5%u6709%u9632%u6C61%u88C5%u7F6E%u548C%u8BBE%u5907%u7684%u8239%u8236%2C%u6267%u6CD5%u8247%2C%u5907%u7528-%u7528%u4E8E%u5F53%u5730%u8239%u8236%u7684%u4EFB%u52A1%u5206%u914D%2C%u5907%u7528-%u7528%u4E8E%u5F53%u5730%u8239%u8236%u7684%u4EFB%u52A1%u5206%u914D%2C%u533B%u7597%u8239%2C%u7B26%u540818%u53F7%u51B3%u8BAE%28Mob-83%29%u7684%u8239%u8236%2C%u62D6%u5F15%u5E76%u4E14%u8239%u957F%3E200m%u6216%u8239%u5BBD%3E25m%2C%u758F%u6D5A%u6216%u6C34%u4E0B%u4F5C%u4E1A%2C%u6F5C%u6C34%u4F5C%u4E1A%2C%u53C2%u4E0E%u519B%u4E8B%u884C%u52A8%2C%u5E06%u8239%u822A%u884C%2C%u5A31%u4E50%u8239%2C%u5730%u6548%u5E94%u8239%2C%u9AD8%u901F%u8239%2C%u5176%u4ED6%u7C7B%u578B%u7684%u8239%u8236%2C%u5176%u4ED6; _elane_shipfilter_length=0%2C40%2C41%2C80%2C81%2C120%2C121%2C160%2C161%2C240%2C241%2C320%2C321%2C9999; _elane_shipfilter_sog=0%2C1; _filter_flag=-1; _elane_shipfilter_olength=; tc_QX=; leadPageCookie=1; Shipxy_VerificationCode=ffa2d72b-f62d-4ff4-948b-6f24a30bc64d; .UserAuth2=8932D7DA6B17BB2E2FE1C4651BCD8419F05642557F6FFD0E908FC8F63D6FB41422FA6DCC97B4DE3094EBA26A67661B053168A0B1E19871850660F261CABA541771A92D7C6694634E4A51A82BEDCEE57C4582616DD5EFF59A43F2F1B63F6422B9EC162987D4848A8DE460212C1C8C635F1D77E041");
//        System.out.println(getPortCongestionFromWebAndSaveToDatabase("_elane_maptype=MT_SEA; Hm_lvt_adc1d4b64be85a31d37dd5e88526cc47=1571897877,1571918706,1573730663; tc_TC=; _elane_shipfilter_type=%u8D27%u8239%2C%u96C6%u88C5%u7BB1%u8239%2C%u6CB9%u8F6E%2C%u5F15%u822A%u8239%2C%u62D6%u8F6E%2C%u62D6%u5F15%2C%u6E14%u8239%2C%u6355%u635E%2C%u5BA2%u8239%2C%u641C%u6551%u8239%2C%u6E2F%u53E3%u4F9B%u5E94%u8239%2C%u88C5%u6709%u9632%u6C61%u88C5%u7F6E%u548C%u8BBE%u5907%u7684%u8239%u8236%2C%u6267%u6CD5%u8247%2C%u5907%u7528-%u7528%u4E8E%u5F53%u5730%u8239%u8236%u7684%u4EFB%u52A1%u5206%u914D%2C%u5907%u7528-%u7528%u4E8E%u5F53%u5730%u8239%u8236%u7684%u4EFB%u52A1%u5206%u914D%2C%u533B%u7597%u8239%2C%u7B26%u540818%u53F7%u51B3%u8BAE%28Mob-83%29%u7684%u8239%u8236%2C%u62D6%u5F15%u5E76%u4E14%u8239%u957F%3E200m%u6216%u8239%u5BBD%3E25m%2C%u758F%u6D5A%u6216%u6C34%u4E0B%u4F5C%u4E1A%2C%u6F5C%u6C34%u4F5C%u4E1A%2C%u53C2%u4E0E%u519B%u4E8B%u884C%u52A8%2C%u5E06%u8239%u822A%u884C%2C%u5A31%u4E50%u8239%2C%u5730%u6548%u5E94%u8239%2C%u9AD8%u901F%u8239%2C%u5176%u4ED6%u7C7B%u578B%u7684%u8239%u8236%2C%u5176%u4ED6; _elane_shipfilter_length=0%2C40%2C41%2C80%2C81%2C120%2C121%2C160%2C161%2C240%2C241%2C320%2C321%2C9999; _elane_shipfilter_sog=0%2C1; _elane_shipfilter_country=1; _filter_flag=-1; _elane_shipfilter_olength=0; tc_QX=; leadPageCookie=1; shipxy_v3_history_serch=s%u2606YANCHENGJI01805%u2606413900011%u260670%u2606MMSI%uFF1A413900011%7Cs%u2606JUFENG829%u2606413903389%u260670%u2606MMSI%uFF1A413903389%7Cp%u2606%u5317%u6D25%u2606213527%u260621.833334%u2606111.966667%7Cp%u2606Gangavaram%u2606218139%u260617.62645%u260683.2484%7Cs%u2606AO%20TOU%203243%u26063243%u2606%u2606MMSI%uFF1A3243%7Cs%u2606AO%20TOU%206789%2002%u2606678902%u2606%u2606MMSI%uFF1A678902%7Cs%u2606TRAILBLAZER%u2606366082000%u260630%u2606IMO%uFF1A7729069%7Cs%u2606SYMI%20I%u2606636016306%u2606100%u2606MMSI%uFF1A636016306%7Cs%u2606DONGPING120651%u260650087102%u2606%u2606MMSI%uFF1A50087102%7Cs%u2606YUEDONGGUANYU92020%u2606412468699%u260630%u2606MMSI%uFF1A412468699; .UserAuth2=9059D37AA149D2FAFC4CA9FF5071381A03B3E18230E95871D35106D820762CE480DE0CCE98FC7C73A166B9DBD583C3BF10DD1BA85B512F4E533DD813D7661F0374581A9008B899B24CA22409FEAE20317F13FFBC7529E4ABB4369E78D9E19921BF91C3475961561E373471F51ED88B935FE9953F; FD857C2AF68165D4=/vvpckUpWYbqsf/AR2UR989/U3+c1rQxrmBBjlg2xoaGuleYSZ0MY3AnhtgfgRtl9yVLHl6r4io=; SERVERID=ce54c768aca7be22386d8a7ce24ecdae|1573734191|1573730667; Hm_lpvt_adc1d4b64be85a31d37dd5e88526cc47=1573730663")
//        );
    }
}
